package Student.Dao;

import Student.Entity.Subject;
import Student.Kit.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SubjectDao {
    //增
    public int add(Subject s) {
        Connection con = DBUtil.getConnection();
        PreparedStatement ps = null;
        String sql = "insert into s_subject(subjectname,classhour,gradeid) values(?,?,?)";
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, s.getSubjectname());
            ps.setInt(2, s.getClasshour());
            ps.setInt(3, s.getGradeid());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(ps);
            DBUtil.close(con);
        }
        return 0;
    }

    //改
    public int update(Subject s) {
        Connection con = DBUtil.getConnection();
        PreparedStatement ps = null;
        String sql = "update s_subject set subjectname=?,classhour=?,gradeid=? where id=?";
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, s.getSubjectname());
            ps.setInt(2, s.getClasshour());
            ps.setInt(3, s.getGradeid());
            ps.setInt(4, s.getId());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(ps);
            DBUtil.close(con);
        }
        return 0;
    }

    //删
    public int delete(int id) {
        Connection con = DBUtil.getConnection();
        String sql = "delete from s_subject where id=?";
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(ps);
            DBUtil.close(con);
        }
        return 0;
    }

    //根据主键查对象
    public Subject find(int id) {
        Connection con = DBUtil.getConnection();
        String sql = "select id,subjectname,classhour,gradeid from s_subject where id=?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                return new Subject(rs.getInt(1), rs.getString(2),
                        rs.getInt(3), rs.getInt(4));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, con);
        }
        return null;
    }

    //查全部
    public List<Subject> search(int page, int line) {
        Connection con = DBUtil.getConnection();
        String sql = "select id,subjectname,classhour,gradeid from s_subject limit ?,?";
        List<Subject> list = new ArrayList<>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement(sql);
            ps.setInt(1, page);
            ps.setInt(2, line);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Subject(rs.getInt("id"), rs.getString("subjectname"), rs.getInt("classhour"), rs.getInt("gradeid")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs, ps, con);
        }
        return list;
    }
}
